{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Guest House - Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "Ruth Cadbury. Show the total amount payable by guest Ruth Cadbury for her room bookings. You should JOIN to the rate table using room_type_requested and occupants.\n",
    "\n",
    "```\n",
    "+--------------------+\n",
    "| SUM(nights*amount) |\n",
    "+--------------------+\n",
    "|             552.00 |\n",
    "+--------------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sum_amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>552.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(552.0,)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(nights*amount) sum_amt\n",
    "FROM booking JOIN guest ON (booking.guest_id=guest.id)\n",
    "  JOIN rate ON (booking.room_type_requested=rate.room_type AND booking.occupants=rate.occupancy)\n",
    "WHERE guest.first_name='Ruth' AND guest.last_name='Cadbury'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Including Extras. Calculate the total bill for booking 5346 including extras.\n",
    "\n",
    "```\n",
    "+-------------+\n",
    "| SUM(amount) |\n",
    "+-------------+\n",
    "|      118.56 |\n",
    "+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sum_amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>118.56</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(118.56,)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(c.amount) sum_amt FROM \n",
    "(SELECT booking.booking_id, amount FROM\n",
    "   booking JOIN rate ON (booking.occupants=rate.occupancy AND \n",
    "                         booking.room_type_requested=rate.room_type)\n",
    " UNION ALL\n",
    " SELECT booking.booking_id, amount FROM\n",
    "   booking JOIN extra ON booking.booking_id=extra.booking_id\n",
    ") AS c\n",
    "WHERE c.booking_id=5346"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "Edinburgh Residents. For every guest who has the word “Edinburgh” in their address show the total number of nights booked. Be sure to include 0 for those guests who have never had a booking. Show last name, first name, address and number of nights. Order by last name then first name.\n",
    "\n",
    "```\n",
    "+-----------+------------+---------------------------+--------+\n",
    "| last_name | first_name | address                   | nights |\n",
    "+-----------+------------+---------------------------+--------+\n",
    "| Brock     | Deidre     | Edinburgh North and Leith |      0 |\n",
    "| Cherry    | Joanna     | Edinburgh South West      |      0 |\n",
    "| Murray    | Ian        | Edinburgh South           |     13 |\n",
    "| Sheppard  | Tommy      | Edinburgh East            |      0 |\n",
    "| Thomson   | Michelle   | Edinburgh West            |      3 |\n",
    "+-----------+------------+---------------------------+--------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>last_name</th>\n",
       "        <th>first_name</th>\n",
       "        <th>address</th>\n",
       "        <th>nights</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brock</td>\n",
       "        <td>Deidre</td>\n",
       "        <td>Edinburgh North and Leith</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cherry</td>\n",
       "        <td>Joanna</td>\n",
       "        <td>Edinburgh South West</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Murray</td>\n",
       "        <td>Ian</td>\n",
       "        <td>Edinburgh South</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sheppard</td>\n",
       "        <td>Tommy</td>\n",
       "        <td>Edinburgh East</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thomson</td>\n",
       "        <td>Michelle</td>\n",
       "        <td>Edinburgh West</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Brock', 'Deidre', 'Edinburgh North and Leith', 0),\n",
       " ('Cherry', 'Joanna', 'Edinburgh South West', 0),\n",
       " ('Murray', 'Ian', 'Edinburgh South', 13),\n",
       " ('Sheppard', 'Tommy', 'Edinburgh East', 0),\n",
       " ('Thomson', 'Michelle', 'Edinburgh West', 3)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT last_name, first_name, address, SUM(COALESCE(nights, 0)) nights\n",
    "  FROM booking RIGHT JOIN guest ON (booking.guest_id=guest.id)\n",
    "    GROUP BY last_name, first_name, address, guest.id\n",
    "    HAVING address LIKE '%Edinburgh%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "How busy are we? For each day of the week beginning 2016-11-25 show the number of bookings starting that day. Be sure to show all the days of the week in the correct order.\n",
    "\n",
    "```\n",
    "+------------+----------+\n",
    "| i          | arrivals |\n",
    "+------------+----------+\n",
    "| 2016-11-25 |        7 |\n",
    "| 2016-11-26 |        8 |\n",
    "| 2016-11-27 |       12 |\n",
    "| 2016-11-28 |        7 |\n",
    "| 2016-11-29 |       13 |\n",
    "| 2016-11-30 |        6 |\n",
    "| 2016-12-01 |        7 |\n",
    "+------------+----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>booking_date</th>\n",
       "        <th>arrivals</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-25</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-26</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-27</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-28</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-29</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-30</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-01</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2016-11-25', 7),\n",
       " ('2016-11-26', 8),\n",
       " ('2016-11-27', 12),\n",
       " ('2016-11-28', 7),\n",
       " ('2016-11-29', 13),\n",
       " ('2016-11-30', 6),\n",
       " ('2016-12-01', 7)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT booking_date, COUNT(booking_id) arrivals \n",
    "  FROM booking\n",
    "    WHERE booking_date BETWEEN '2016-11-25' AND DATE_ADD('2016-11-25', 6)\n",
    "    GROUP BY booking_date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "How many guests? Show the number of guests in the hotel on the night of 2016-11-21. Include all occupants who checked in that day but not those who checked out.\n",
    "\n",
    "```\n",
    "+----------------+\n",
    "| SUM(occupants) |\n",
    "+----------------+\n",
    "|             39 |\n",
    "+----------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sum_occupants</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(39,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(occupants) AS sum_occupants\n",
    "  FROM booking\n",
    "    WHERE booking_date <= '2016-11-21' AND \n",
    "      DATE_ADD(booking_date, nights) > '2016-11-21'"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
